﻿using System;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace DbManager
{
    /// <summary>
    /// 数据库底层类
    /// </summary>
    public class DbDataBase
    {
        #region 字段
        //底层类
        private Database _database;
        //sql参数名称集合
        private List<string> _paraname;
        //sql参数类型集合
        private List<string> _paratype;
        //sql参数值集合
        private List<string> _paravalue;
        //数据库连接类
        private DbConnection _dbconnection;
        //事物
        private DbTransaction _dbTran;
        //批量写入数据库
        private SqlBulkCopy _sqlBc;
        #endregion
        #region 属性
        /// <summary>
        /// 得到数据库连接
        /// </summary>
        protected DbConnection dbconnection
        {
            get
            {
                CreateConnection();
                return _dbconnection;
            }
        }
        /// <summary>
        /// 得到事物
        /// </summary>
        protected DbTransaction DbTran
        {
            get
            {
                CreateConnection();
                _dbTran = _dbconnection.BeginTransaction();
                return _dbTran;
            }
            set
            {
                _dbTran = value;
            }
        }
        /// <summary>
        /// 批量写入数据库
        /// </summary>
        protected SqlBulkCopy SqlBc
        {
            get
            {
                _sqlBc = new SqlBulkCopy((SqlConnection)_dbconnection, SqlBulkCopyOptions.Default, (SqlTransaction)_dbTran);
                return _sqlBc;
            }
            set
            {
                _sqlBc = value;
            }
        }
        #endregion
        #region 公共方法
        /// <summary>
        /// 构造函数
        /// </summary>
        public DbDataBase()
        {
            #region
            //初始sql参数名称集合
            _paraname = new List<string>();
            //初始sql参数类型集合
            _paratype = new List<string>();
            //初始sql参数值集合
            _paravalue = new List<string>();
            //初始底层类
            _database = new SqlDatabase(ConnectionString.sqlCon);
            #endregion
        }
        /// <summary>
        /// 打开连接
        /// </summary>
        private void CreateConnection()
        {
            #region
            if (_dbconnection == null)
            {
                _dbconnection = _database.CreateConnection();
                //获取连接字符串
                _dbconnection.ConnectionString = ConnectionString.sqlCon;
                _dbconnection.Open();
            }
            #endregion
        }
        /// <summary>
        /// 关闭连接
        /// </summary>
        protected void DbConnectionColse()
        {
            #region
            if (_dbconnection != null)
            {
                _dbconnection.Close();
                _dbconnection = null;
            }
            #endregion
        }
        /// <summary>
        /// 获取DbCommand
        /// </summary>
        /// <param name="query">sql语句</param>
        protected DbCommand GetSqlStringCommand(string query)
        {
            #region
            //清空参数集合
            _paraname.Clear();
            _paratype.Clear();
            _paravalue.Clear();
            //得到DbCommand对象
            DbCommand sqlStringCommand = _database.GetSqlStringCommand(query);
            //设置超时时间
            sqlStringCommand.CommandTimeout = 0xe10;
            return sqlStringCommand;
            #endregion
        }
        /// <summary>
        /// 初始化DbCommand字段属性的值
        /// </summary>
        /// <param name="command">DbCommand对象</param>
        /// <param name="name">参数名称</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="value">参数集合</param>
        protected void AddInParameter(DbCommand command, string name, DbType dbType, object value)
        {
            #region
            //利用AddInParameter方法给DbCommand对象的字段属性赋值
            _database.AddInParameter(command, name, dbType, value);
            if (value == null)
            {
                value = "";
            }
            _paraname.Add(name);
            _paratype.Add(dbType.ToString());
            _paravalue.Add(value.ToString());
            #endregion
        }
        /// <summary>
        /// 添加返回值的参数
        /// </summary>
        /// <param name="command">DbCommand对象</param>
        /// <param name="name">参数名称</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="value">参数集合</param>
        protected void AddOutParameter(DbCommand command, string name, DbType dbType, int value)
        {
            #region
            //利用AddInParameter方法给DbCommand对象的字段属性赋值
            _database.AddOutParameter(command, name, dbType, value);
            _paraname.Add(name);
            _paratype.Add(dbType.ToString());
            _paravalue.Add(value.ToString());
            #endregion
        }
        /// <summary>
        /// 返回查询结果集
        /// </summary>
        /// <param name="command">DbCommand对象</param>
        public DataSet ExecuteDataSet(DbCommand command)
        {
            #region
            command.CommandTimeout = 0xe10;
            return _database.ExecuteDataSet(command);
            #endregion
        }
        /// <summary>
        /// 执行带事物的操作
        /// </summary>
        /// <param name="command"></param>
        /// <param name="dbtr"></param>
        /// <returns></returns>
        internal int ExecuteUpdate(DbCommand command, DbTransaction dbtr)
        {
            #region
            command.CommandTimeout = 0xe10;
            return _database.ExecuteNonQuery(command, dbtr);
            #endregion
        }
        internal object ExecuteScalar(DbCommand command, DbTransaction dbtr)
        {
            #region
            command.CommandTimeout = 0xe10;
            return _database.ExecuteScalar(command, dbtr);
            #endregion
        }
        #endregion
    }
}
